package com.example.Function;


import com.alibaba.druid.pool.DruidDataSourceFactory;
import com.example.Object.Person;
import com.example.Object.Question;


import com.example.Object.Score;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;

import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 数据库操作类
 * @author Echo
 */
public class DataBaseOp {
    private static JdbcTemplate JDBC_OP;
    //上传的试题名，临时存放
    public static String publicTile;
    static {

        try {
            //初始化连接池

            Properties pro = new Properties();
            InputStream resource = DataBaseOp.class.getClassLoader().getResourceAsStream("druid.properties");

            pro.load(resource);
            JDBC_OP = new JdbcTemplate(DruidDataSourceFactory.createDataSource(pro));

        } catch (Exception e) {
            System.out.println("error");
            e.printStackTrace();
        }
    }

    /**
     * 新建一个试题表
     * @param tName 科目名
     */
    public static void creatTable(String tName){
        String sql = "create table if not exists `"+tName+"`(`Question` varchar(1000),`A` varchar(1000),`B` varchar(1000),`C` varchar(1000),`D` varchar(1000),`right` varchar(1000),`type` int);";
        JDBC_OP.execute(sql);
    }
    public static void creatErrorBook(String tName){
        String sql = "create table if not exists `"+tName+"`(`Question` varchar(1000),`A` varchar(1000),`B` varchar(1000),`C` varchar(1000),`D` varchar(1000),`right` varchar(1000),`type` int,`email` varchar(30));";
        JDBC_OP.execute(sql);
    }
    /**
     * 插入一个问题
     * @param q 问题对象
     * @param title 试题名
     * @return 插入状态
     */
    public static boolean insertQuestion(Question q,String title){
        System.out.println(q);
        String sql = "insert into singlechoice values(?,?,?,?,?,?)";
        int update = JDBC_OP.update(sql, q.getQuestion(), q.getA(), q.getB(), q.getC(), q.getD(), q.getRight());
        return  update > 0;
    }

    /**
     * 插入一个试题集合
     * @param qs 问题集合
     * @param title 试题名
     * @return 插入状态
     */
    public static int insertQuestion(List<Question> qs,String title){
        int update = 0;
        String sql = "insert into "+title+" values(?,?,?,?,?,?,?)";
        for(Question q : qs) {
            update += JDBC_OP.update(sql,q.getQuestion(), q.getA(), q.getB(), q.getC(), q.getD(), q.getRight(),q.getType());
        }
        publicTile = null;
        return update;
    }
    //错题本
    public static int insertErrorQuestion(List<Question> qs,String title,String email){
        int update = 0;
        String sql = "insert into "+title+" values(?,?,?,?,?,?,?,?)";
        for(Question q : qs) {
            update += JDBC_OP.update(sql,q.getQuestion(), q.getA(), q.getB(), q.getC(), q.getD(), q.getRight(),q.getType(),email);
        }
        return update;
    }
    /**
     * 得到所有的试题集名称
     * @return 试题集名称
     */
    public static List<List<String>> getQuestionTileList(){
        List<String> title = JDBC_OP.queryForList("select subjectname from tablename", String.class);
        List<String> code = JDBC_OP.queryForList("select subjectcode from tablename", String.class);
        List<String> user = JDBC_OP.queryForList("select user from tablename", String.class);
        List<String> name = new ArrayList<>();
        for (String s : user) {
            String s1 = JDBC_OP.queryForObject("select name from usersinfor where email = ?", String.class, s);
            name.add(s1);
        }
        ArrayList<List<String>> total = new ArrayList<>();
        total.add(title);
        total.add(name);
        total.add(code);

        return total;
    }

    /**
     * 在表名集合中插入一个表名
     * @param title 表明
     * @return 插入状态
     */
    public static boolean insertTableName(String title,String id,String user){
        String sql = "insert into tablename (subjectname,subjectcode,user,num) values(?,?,?,null )";
        int update = JDBC_OP.update(sql, title,id,user);
        return update > 0;
    }

    /**
     * 插入一个错题表名
     * @param title 试题名称
     * @param id 试题id
     * @param user 创建者email
     * @param eid 错题表id
     * @return
     */
    public static boolean insertErrorBookName(String title,String id,String user,String eid){
        String sql = "insert into errorbook (subjectcode,errorcode,subjectname,email) values(?,?,?,?)";
        int update = JDBC_OP.update(sql,id,eid,title,user);
        return update > 0;
    }

    /**
     * 得到一个试题的全部名题目,封装成Question类返回
     * @param id 试题id
     * @return Question集合
     */
    public static List<Question> getQuestionList(String id){
        String sql = "select * from "+id;
        return JDBC_OP.query(sql, new BeanPropertyRowMapper<>(Question.class));
    }

    /**
     * 得到一个错题本中某个用户的试题集合
     * @param id 错题本id
     * @param email 用户
     * @return 错题对象集合
     */
    public static List<Question> getErrorQuestionList(String id,String email){
        String sql = "select * from "+id +" where email = '"+email+"'";
        return JDBC_OP.query(sql, new BeanPropertyRowMapper<>(Question.class));
    }

    /**
     * 插入一个用户信息
     * @param p 用户对象
     * @return 状态
     */
    public static boolean insertUserInfo(Person p){
        String sql = "insert into usersinfor value(?,?,?,?)";
        int update = JDBC_OP.update(sql, p.getName(), p.getEmail(), p.getPassword(), p.getRole());
        return update > 0;
    }

    /**
     * 检查登录账号是否成功
     * @param email 账号
     * @param password 密码
     * @return 状态
     */
    public static boolean checkUser(String email,String password){
        String sql = "select count(email) from usersinfor where email = ? and password = ?";
        Integer integer = JDBC_OP.queryForObject(sql, int.class, email, password);
        return integer != 0;
    }

    /**
     * 更改一个用户的密码
     * @param password 更改后的密码
     * @param user 更改的用户
     */
    public static void changePassword(String password,String user){
        String sql = "update usersinfor set password = ? where email = ?";
        JDBC_OP.update(sql,password,user);
    }

    /**
     * 得到个人发布的试题名列表
     * @param email 账号
     * @return <表名,表id>
     */
    public static List<List<String>> getIndividualQuestion(String email){

        List<String> subjectName = JDBC_OP.queryForList("select subjectname from tablename where user = ?",String.class,email);
        List<String> subjectCode = JDBC_OP.queryForList("select subjectcode from tablename where user = ?",String.class,email);

        List<List<String>> total = new ArrayList<>();
        System.out.println(subjectCode.size());
        total.add(subjectName);
        total.add(subjectCode);
        return total;
    }

    /**
     * 得到全部的试题表名
     * @return <表名,表id>
     */
    public static List<List<String>> getAllQuestion(){

        List<String> subjectName = JDBC_OP.queryForList("select subjectname from tablename",String.class);
        List<String> subjectCode = JDBC_OP.queryForList("select subjectcode from tablename",String.class);

        List<List<String>> total = new ArrayList<>();

        total.add(subjectName);
        total.add(subjectCode);
        return total;
    }

    /**
     * 删除试题
     * @param tableCode 试题id
     * @return 状态
     */
    public static boolean deleteTable(String tableCode){
        String sql = "DROP TABLE IF EXISTS "+tableCode;
        int update = JDBC_OP.update(sql);
        return  update >= 0;
    }

    /**
     * 删除表名
     * @param tableCode 表id
     */
    public static void deleteTableName(String tableCode){
        String sql = "delete from tablename where subjectcode = ?";
        JDBC_OP.update(sql,tableCode);
    }

    /**
     * 删除错题表名
     * @param tableCode 错题id
     */
    public static void deleteErrorBookName(String tableCode){
        String sql = "delete from errorbook where subjectcode = ?";
        JDBC_OP.update(sql,tableCode);
    }

    /**
     * 删除某试题的分数，删除试题时同步删除相应的分数
     * @param tableCode 表id
     */
    public static void deleteScore(String tableCode){
        JDBC_OP.update("delete from score where subjectcode = ?",tableCode);
    }

    /**
     * 重复名检查
     * @param email 用户邮箱
     * @return 注册与否
     */
    public static Integer registerCheck(String email){
        String sql = "SELECT COUNT('email') FROM usersinfor WHERE email = ?";
        return JDBC_OP.queryForObject(sql,Integer.class,email);
    }

    /**
     * 得用户到最近一次的某试题成绩
     * @param email 用户
     * @param subjectCode 试题id
     * @return 上一次的成绩
     */
    public static Integer getRecentScore(String email,String subjectCode){
        String sql = "select score from score where useremail = ? and subjectcode = ?";
        List<Integer> scores = JDBC_OP.queryForList(sql, Integer.class, email, subjectCode);
        if(scores.size() != 0){
            return scores.get(scores.size()-1);
        }
        return -1;
    }

    /**
     * 通过试题id得到表名
     * @param subjectCode 表id
     * @return 表名
     */
    public static String getSubjectName(String subjectCode){
        return JDBC_OP.queryForObject("select subjectname from tablename where subjectcode = ?",String.class,subjectCode);
    }

    /**
     * 通过email得到用户名
     * @param email 用户email
     * @return 用户的名字
     */
    public static String getUsernameWithEmail(String email){
        return JDBC_OP.queryForObject("select name from usersinfor where email = ?",String.class,email);
    }

    /**
     * 保存成绩，完成尸体后调用
     * @param email 用户
     * @param subjectCode 试题id
     * @param score 分数
     */
    public static void saveScore(String email,String subjectCode,Double score){
        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String time = df.format(new Date());
        String sql = "insert into score(subjectname,subjectcode,username,useremail,score,time) VALUES(?,?,?,?,?,?)";
        JDBC_OP.update(sql,getSubjectName(subjectCode),subjectCode,getUsernameWithEmail(email),email,score,time);
    }

    /**
     * 得到所某用户的所有有成绩的科目以及分数
     * @param email 用户
     * @return 有成绩的科目
     */
    public static List<List<String>> getIndividualSubject(String email){
        List<String> name = JDBC_OP.queryForList("SELECT subjectname FROM (SELECT DISTINCT subjectcode,subjectname FROM score WHERE useremail = ?) AS a ", String.class, email);
        List<String> code = JDBC_OP.queryForList("SELECT DISTINCT subjectcode FROM score WHERE useremail = ?", String.class, email);
        ArrayList<List<String>> total = new ArrayList<>();
        total.add(name);
        total.add(code);
        return total;
    }

    /**
     * 得到用户的某科目分数列表
     * @param email 用户
     * @param subjectCode 试题id
     * @return 分数列表
     */
    public static List<Score> getQuestionScoreList(String email,String subjectCode){
        String sql = "select * from score where useremail = ? and subjectcode = ?";
        return JDBC_OP.query(sql, new BeanPropertyRowMapper<>(Score.class), email, subjectCode);
    }

    /**
     * 得到某试题的错题id
     * @param subjectCode 试题id
     * @return 错题本id
     */
    public static String getErrorCode(String subjectCode){
        return JDBC_OP.queryForObject("select errorcode from errorbook where subjectcode = ?",String.class,subjectCode);
    }

    /**
     * 删除错题，在错题本中答对的删除
     * @param question 答对的问题
     * @param eCode 错题本id
     * @param email 用户email
     */
    public static void deleteErrorQuestion(List<String> question,String eCode,String email){
        for (String s : question) {
            String sql = "DELETE FROM "+eCode+" WHERE Question = '"+s+"' and email = '"+email+"' LIMIT 1";
            JDBC_OP.update(sql);
        }

    }
}

